package org.groupg.practice.jdbi;

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;
import org.jdbi.v3.sqlobject.statement.GetGeneratedKeys;

import java.util.List;

public class Jdbi3Example {

    public static void main(String[] args) {
        // 1. 初始化H2内存数据库和JDBI
        Jdbi jdbi = Jdbi.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");

        // 2. 创建表结构
        jdbi.useHandle(handle -> {
            handle.execute("CREATE TABLE organization (" +
                    "id IDENTITY PRIMARY KEY, " +
                    "name VARCHAR(100) NOT NULL)");

            handle.execute("CREATE TABLE user (" +
                    "id IDENTITY PRIMARY KEY, " +
                    "name VARCHAR(50) NOT NULL, " +
                    "email VARCHAR(100) NOT NULL, " +
                    "organization_id INT, " +
                    "FOREIGN KEY (organization_id) REFERENCES organization(id))");
        });

        // 3. 获取DAO实例
        OrganizationDao orgDao = jdbi.onDemand(OrganizationDao.class);
        UserDao userDao = jdbi.onDemand(UserDao.class);

        // ================= 机构表操作示例 =================
        // 插入机构
        long googleId = orgDao.insert("Google");
        long microsoftId = orgDao.insert("Microsoft");
        System.out.println("插入机构ID: " + googleId + ", " + microsoftId);

        // 查询所有机构
        List<Organization> orgs = orgDao.listAll();
        System.out.println("\n所有机构:");
        orgs.forEach(org -> System.out.println(org.id + ": " + org.name));

        // 更新机构
        orgDao.update(microsoftId, "Microsoft Corp");
        System.out.println("\n更新后的Microsoft: " + orgDao.findById(microsoftId));

        // ================= 用户表操作示例 =================
        // 插入用户
        long aliceId = userDao.insert("Alice", "alice@google.com", googleId);
        long bobId = userDao.insert("Bob", "bob@microsoft.com", microsoftId);
        System.out.println("\n插入用户ID: " + aliceId + ", " + bobId);

        // 查询所有用户
        System.out.println("\n所有用户:");
        userDao.listAll().forEach(System.out::println);

        // 根据机构查询用户
        System.out.println("\nGoogle员工:");
        userDao.findByOrganization(googleId).forEach(System.out::println);

        // 更新用户邮箱
        userDao.updateEmail(aliceId, "alice.new@google.com");
        System.out.println("\n更新后的Alice: " + userDao.findById(aliceId));

        // 删除用户
        userDao.delete(bobId);
        System.out.println("\n删除Bob后剩余用户:");
        userDao.listAll().forEach(System.out::println);
    }

    // ================= 数据模型 =================
    static class Organization {
        long id;
        String name;

        public Organization(long id, String name) {
            this.id = id;
            this.name = name;
        }

        @Override
        public String toString() {
            return "Organization{" + id + ", '" + name + "'}";
        }
    }

    static class User {
        long id;
        String name;
        String email;
        Long organizationId;

        public User(long id, String name, String email, Long organizationId) {
            this.id = id;
            this.name = name;
            this.email = email;
            this.organizationId = organizationId;
        }

        @Override
        public String toString() {
            return "User{" + id + ", '" + name + "', '" + email + "', org=" + organizationId + "}";
        }
    }

    // ================= DAO接口(机构表) =================
    interface OrganizationDao {
        @SqlUpdate("INSERT INTO organization(name) VALUES (:name)")
        @GetGeneratedKeys("id")
        long insert(@Bind("name") String name);

        @SqlQuery("SELECT * FROM organization")
        List<Organization> listAll();

        @SqlQuery("SELECT * FROM organization WHERE id = :id")
        Organization findById(@Bind("id") long id);

        @SqlUpdate("UPDATE organization SET name = :name WHERE id = :id")
        void update(@Bind("id") long id, @Bind("name") String newName);
    }

    // ================= DAO接口(用户表) =================
    interface UserDao {
        @SqlUpdate("INSERT INTO user(name, email, organization_id) " +
                   "VALUES (:name, :email, :orgId)")
        @GetGeneratedKeys("id")
        long insert(
            @Bind("name") String name,
            @Bind("email") String email,
            @Bind("orgId") Long organizationId
        );

        @SqlQuery("SELECT * FROM user")
        List<User> listAll();

        @SqlQuery("SELECT * FROM user WHERE organization_id = :orgId")
        List<User> findByOrganization(@Bind("orgId") long orgId);

        @SqlQuery("SELECT * FROM user WHERE id = :id")
        User findById(@Bind("id") long id);

        @SqlUpdate("UPDATE user SET email = :email WHERE id = :id")
        void updateEmail(@Bind("id") long id, @Bind("email") String newEmail);

        @SqlUpdate("DELETE FROM user WHERE id = :id")
        void delete(@Bind("id") long id);
    }
}